/* build_ucr.do file
files used: "UCR/Data - Raw/Crime_Data" files
files created "UCR/Data - Clean/Crime_Data/UCR_1991_2016.dta"
programs used: build_ucr.do
*/
********************************************************************************

** Append years 1991-2016
use "Raw Data/UCR/Crime Data/ucr1991.dta", clear

forvalues y = 1991(1)2016{
	append using "Raw Data/UCR/Crime Data/ucr`y'.dta"
}

*adjust year
forvalues x = 91(1)99 {
replace year = 19`x' if year == `x'
}

forvalues x = 0(1)9 {
replace year = 200`x' if year == `x'
}

forvalues x = 10(1)16 {
replace year = 20`x' if year == `x'
}


** Recode crime variables
rename c1f1_* murder_*
rename c1f2_* manslaughter_*
rename c1f11_* total_assaults_*
rename c1f12_* gun_assaults_*
rename c1f13_* knife_assaults_*
rename c1f14_* other_assaults_*
rename c1f15_* hand_assaults_*
rename c1f16_* simple_assaults_*
rename c1f26_* total_crime_*

** Rename ORI
rename ori ori7

** Keep only agencies that report 12 months/year
keep if num_month == "12"

** Drop agency months that report zero crime
egen min_tot = rmin(total_crime_*)
drop if min_tot == 0

** Drop negative monthly crime values as well as missing total crime
foreach var of varlist murder_* manslaughter_* total_assaults_* gun_assaults_* ///
	knife_assaults_* other_assaults_* hand_assaults_* simple_assaults_* total_crime_* {
		drop if `var' < 0
}

foreach var of varlist total_crime_* {
	drop if `var' == .
}


** Merge with ori-fips crosswalk
merge m:1 ori7 using "Raw Data/UCR/Crosswalk Data/ORI_FIPS_CROSSWALK_clean_UCR.dta"
keep if _merge==3
drop _merge

egen population = rsum(pop1 pop2 pop3)

** Collapse to ori year level
collapse (sum) murder_* (mean) population, /// 
	by(ori9 fips fips_st statename year)

** Reshape long to ori month level
reshape long murder_, ///
	i(ori9 fips fips_st statename year) j(month) 
 
merge m:1 fips using "Raw Data/county lat lon/us_county_latlng.dta"
keep if _m != 2
drop _m
 
** Recode some counties that got renamed
replace fips=51059 if fips==51610
replace fips=51163 if fips==51678

** Recode state variable for merge
replace statename=lower(statename)
rename statename state
 
*save
save "Processed Data/UCR_1991_2016.dta", replace




